package arch.dao.impl.statistics;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import javax.annotation.Resource;

import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import arch.dao.statistics.LoanStatisticsDao;
import arch.domain.archive.Category;
import arch.domain.archive.Retention;
import arch.domain.statistics.LoanStatistics;
/**
 * 
 * @author <a mailto="wanglong635@126.com">Long</a>
 *
 */
@Repository("loanStatisticsDao")
public class LoanStatisticsImpl implements LoanStatisticsDao {
	@Resource private JdbcTemplate jdbcTemplate;
	
	@SuppressWarnings("unchecked")
	public List<LoanStatistics> findComplexList(String sql, Object... objects) {
		return this.jdbcTemplate.query(sql, objects,new RowMapper(){
			public Object mapRow(ResultSet rs, int i) throws SQLException {
				LoanStatistics loan = new LoanStatistics();
				Category category = new Category();
				category.setId(rs.getInt("CATEGORY_ID"));
				category.setName(rs.getString("CATEGORY_NAME"));
				loan.setCategory(category);
				
				Retention retention = new Retention();
				retention.setId(rs.getInt("RETENTION_ID"));
				retention.setName(rs.getString("RETENTION_NAME"));
				retention.setYears(rs.getInt("RETENTION_YEARS"));
				loan.setRetention(retention);
				
				loan.setLoanerDeptCode(rs.getString("LOAN_DEPT_CODE"));
				loan.setLoanerDeptName(rs.getString("LOAN_DEPT_NAME"));
				loan.setFiles(rs.getInt("Count"));
				return loan;
			}});
	}

	@SuppressWarnings("unchecked")
	public List<LoanStatistics> findSimpleList(String sql, Object... objects) {
		return this.jdbcTemplate.query(sql, objects,new RowMapper(){
			public Object mapRow(ResultSet rs, int i) throws SQLException {
				LoanStatistics loan = new LoanStatistics();
				loan.setLoanerDeptCode(rs.getString("LOAN_DEPT_CODE"));
				loan.setLoanerDeptName(rs.getString("LOAN_DEPT_NAME"));
				loan.setFiles(rs.getInt("Count"));
				
				return loan;
			}});
	}

	public LoanStatistics findComplex(LoanStatistics unique) {
		String sql = " select ID, YEAR, CLASSIFICATION_KEY, CATEGORY_ID, CATEGORY_NAME, "
			+"  RETENTION_ID, RETENTION_NAME, RETENTION_YEARS, LOAN_DEPT_CODE, LOAN_DEPT_NAME, COUNT "
			+" from LN_COMPLEX_STATISTICS where (YEAR,CLASSIFICATION_KEY,CATEGORY_ID,RETENTION_ID,LOAN_DEPT_CODE) "
			+" =(?,?,?,?,?) ";
		
		try {
			return (LoanStatistics)this.jdbcTemplate.queryForObject(sql,new Object[]{
					unique.getYear(),
					unique.getClassificationKey(),
					unique.getCategory()==null?0:unique.getCategory().getId(),
					unique.getRetention()==null?0:unique.getRetention().getId(),
					unique.getLoanerDeptCode()
			}, new RowMapper(){
				public Object mapRow(ResultSet rs, int i) throws SQLException {
					LoanStatistics loan = new LoanStatistics();
					loan.setId(rs.getString("ID"));
					loan.setYear(rs.getInt("YEAR"));
					loan.setClassificationKey(rs.getString("CLASSIFICATION_KEY"));
					Category category = new Category();
					category.setId(rs.getInt("CATEGORY_ID"));
					category.setName(rs.getString("CATEGORY_NAME"));
					loan.setCategory(category);
					
					Retention retention = new Retention();
					retention.setId(rs.getInt("RETENTION_ID"));
					retention.setName(rs.getString("RETENTION_NAME"));
					retention.setYears(rs.getInt("RETENTION_YEARS"));
					loan.setRetention(retention);
					
					loan.setLoanerDeptCode(rs.getString("LOAN_DEPT_CODE"));
					loan.setLoanerDeptName(rs.getString("LOAN_DEPT_NAME"));
					loan.setFiles(rs.getInt("Count"));
					return loan;
				}});
		} catch (EmptyResultDataAccessException e) {
			return null;
		}catch (Exception e) {
			throw new RuntimeException(e);
		}
	}

	public LoanStatistics findSimple(LoanStatistics unique) {
		String sql = " select ID, YEAR, LOAN_DEPT_CODE, LOAN_DEPT_NAME, COUNT "
			+" from LN_SIMPLE_STATISTICS where (year,LOAN_DEPT_CODE) = (?,?) ";
		try {
			return (LoanStatistics)this.jdbcTemplate.queryForObject(sql,new Object[]{unique.getYear(),unique.getLoanerDeptCode()}, 
					new RowMapper(){
						public Object mapRow(ResultSet rs, int i)
								throws SQLException {
							LoanStatistics loan = new LoanStatistics();
							loan.setId(rs.getString("ID"));
							loan.setYear(rs.getInt("YEAR"));
							loan.setLoanerDeptCode(rs.getString("LOAN_DEPT_CODE"));
							loan.setLoanerDeptName(rs.getString("LOAN_DEPT_NAME"));
							loan.setFiles(rs.getInt("Count"));
							
							return loan;
						}});
		} catch (EmptyResultDataAccessException e) {
			return null;
		} catch (Exception e) {
			throw new RuntimeException(e);
		}
	}

	public void insertComplex(LoanStatistics complex) {
		String sql = "insert into LN_COMPLEX_STATISTICS (ID, YEAR, CLASSIFICATION_KEY, CATEGORY_ID, CATEGORY_NAME,"
			+"RETENTION_ID, RETENTION_NAME, RETENTION_YEARS, LOAN_DEPT_CODE, LOAN_DEPT_NAME, COUNT) values (?,?,?,?,?,?,?,?,?,?,?) ";
		
		this.jdbcTemplate.update(sql,new Object[]{
				complex.getId(),
				complex.getYear(),
				complex.getClassificationKey(),
				complex.getCategory()==null?0:complex.getCategory().getId(),
				complex.getCategory()==null?null:complex.getCategory().getName(),
				complex.getRetention()==null?0:complex.getRetention().getId(),
				complex.getRetention()==null?null:complex.getRetention().getName(),
				complex.getRetention()==null?null:complex.getRetention().getYears(),
				complex.getLoanerDeptCode(),
				complex.getLoanerDeptName(),
				complex.getFiles()
		});
		
	}

	public void insertSimple(LoanStatistics simple) {
		String sql =" insert into LN_SIMPLE_STATISTICS ( ID, YEAR, LOAN_DEPT_CODE, LOAN_DEPT_NAME, COUNT )"
			+" values (?,?,?,?,?)";
		this.jdbcTemplate.update(sql,new Object[]{
				simple.getId(),
				simple.getYear(),
				simple.getLoanerDeptCode(),
				simple.getLoanerDeptName(),
				simple.getFiles()
		});
		
	}

	public void updateComplex(LoanStatistics primaryKey) {
		String sql = "update LN_COMPLEX_STATISTICS set COUNT = ? where ID = ? ";
		this.jdbcTemplate.update(sql,new Object[]{primaryKey.getFiles(),primaryKey.getId()});
	}

	public void updateSimple(LoanStatistics primaryKey) {
		String sql = "update LN_SIMPLE_STATISTICS set COUNT = ? where ID = ? ";
		this.jdbcTemplate.update(sql,new Object[]{primaryKey.getFiles(),primaryKey.getId()});
	}

}
